﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CMS_GYM
{
    class clsCongNoCauLongDAO
    {
        #region Attribute
        /// <summary>
        /// Kết nối SQL Server
        /// </summary>
        private CSQLServer sqlServer = new CSQLServer();
        #endregion

        /// <summary>
        /// Lấy danh sách chức năng
        /// </summary>
        public DataTable LayBang()
        {
            string sql = "sp_GetBangCongNoCauLong";
            DataTable table = sqlServer.readData(sql);
            return table;
        }

        public DataTable LayBangCongNoTheoThangNam(int Thang, int Nam)
        {
            string sql = "sp_GetBangCongNoCauLongTheoThangNam";
            string[] ParameterColection = new string[2] { "@Thang", "@Nam" };
            Object[] valueofParameter = new Object[2]{Thang, Nam};
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public DataTable LayThongTinCongNo(int CongNoId)
        {
            string sql = " ";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@CongNoId";
            valueofParameter[0] = CongNoId;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public DataTable LayBangCongNoChuaThu(decimal KhachHangId)
        {
            string sql = "spGetBangCongNoCauLongChuThuTheoKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@KhachHangId";
            valueofParameter[0] = KhachHangId;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public DataTable LayBangCongNo(decimal KhachHangId)
        {
            string sql = "spGetBangCongNoCauLongTheoKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@KhachHangId";
            valueofParameter[0] = KhachHangId;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public List<clsCongNoCauLongDTO> LayDanhSachCongNo(decimal KhacHangId)
        {
            List<clsCongNoCauLongDTO> CacCongNo = new List<clsCongNoCauLongDTO>();
            DataTable tabCongNo = LayBangCongNo(KhacHangId);
            if (tabCongNo == null)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < tabCongNo.Rows.Count; i++)
                {
                    clsCongNoCauLongDTO CongNo = ChuyenDoi(tabCongNo.Rows[i]);
                    if (CongNo == null)
                    {
                        return null;
                    }
                    else
                    {
                        CacCongNo.Add(CongNo);
                    }
                }
                return CacCongNo;
            }
        }

        public List<clsCongNoCauLongDTO> LayDanhSachCongNoChuaThu(decimal KhacHangId)
        {
            List<clsCongNoCauLongDTO> CacCongNo = new List<clsCongNoCauLongDTO>();
            DataTable tabCongNo = LayBangCongNoChuaThu(KhacHangId);
            if (tabCongNo == null)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < tabCongNo.Rows.Count; i++)
                {
                    clsCongNoCauLongDTO CongNo = ChuyenDoi(tabCongNo.Rows[i]);
                    if (CongNo == null)
                    {
                        return null;
                    }
                    else
                    {
                        CacCongNo.Add(CongNo);
                    }
                }
                return CacCongNo;
            }
        }

        private clsCongNoCauLongDTO ChuyenDoi(DataRow Dong)
        {
            clsCongNoCauLongDTO CongNo = new clsCongNoCauLongDTO();
            if (Dong != null)
            {
                CongNo.CongNoId = int.Parse(Dong["CongNoId"].ToString());
                CongNo.KhachHangId = int.Parse(Dong["KhachHangId"].ToString());
                CongNo.HoTen = Dong["HoTen"] == null ? string.Empty : Dong["HoTen"].ToString();
                CongNo.SoTien = double.Parse(Dong["SoTien"].ToString());
                CongNo.Thang = int.Parse(Dong["Thang"].ToString());
                CongNo.Nam = int.Parse(Dong["Nam"].ToString());
                CongNo.TrangThaiGachNo = bool.Parse(Dong["TrangThaiGachNo"].ToString()) == true ? 1 : 0;
                CongNo.PhieuThuId = Dong["PhieuThuId"] == null || string.IsNullOrEmpty(Dong["PhieuThuId"].ToString()) ? 0 : int.Parse(Dong["PhieuThuId"].ToString());
                CongNo.SoTienThu = Dong["SoTienThu"] == null || string.IsNullOrEmpty(Dong["SoTienThu"].ToString()) ? 0 : double.Parse(Dong["SoTienThu"].ToString());
                CongNo.GhiChu = Dong["GhiChu"] == null || string.IsNullOrEmpty(Dong["GhiChu"].ToString()) ? string.Empty : Dong["GhiChu"].ToString();
                return CongNo;
            }
            return null;
        }

        private clsCongNoCauLongDTO ChuyenDoi(DataTable table)
        {
            clsCongNoCauLongDTO CongNo = new clsCongNoCauLongDTO();
            if (table.Rows.Count == 1)
            {
                DataRow Dong = table.Rows[0];
                CongNo.CongNoId = int.Parse(Dong["CongNoId"].ToString());
                CongNo.KhachHangId = int.Parse(Dong["KhachHangId"].ToString());
                CongNo.HoTen = Dong["HoTen"] == null ? string.Empty : Dong["HoTen"].ToString();
                CongNo.SoTien = double.Parse(Dong["SoTien"].ToString());
                CongNo.Thang = int.Parse(Dong["Thang"].ToString());
                CongNo.Nam = int.Parse(Dong["Nam"].ToString());
                CongNo.TrangThaiGachNo = bool.Parse(Dong["TrangThaiGachNo"].ToString()) == true ? 1 : 0;
                CongNo.PhieuThuId = Dong["PhieuThuId"] == null || string.IsNullOrEmpty(Dong["PhieuThuId"].ToString()) ? 0 : int.Parse(Dong["PhieuThuId"].ToString());
                CongNo.SoTienThu = Dong["SoTienThu"] == null || string.IsNullOrEmpty(Dong["SoTienThu"].ToString()) ? 0 : double.Parse(Dong["SoTienThu"].ToString());
                CongNo.GhiChu = Dong["GhiChu"] == null ? string.Empty : Dong["GhiChu"].ToString();
                return CongNo;
            }
            return null;
        }
       /// Thêm chức năng
       /// </summary>
       /// <param name="ChucNang"></param>
       /// <returns></returns>
        public int Them(clsCongNoCauLongDTO CongNo)
        {
            int i = -1;
            string sql = "sp_InsertCongNoCauLong";
            string[] ParameterColection = new string[6]{"@KhachHangId","@HoTen","@SoTien","@Thang", "@Nam","@TrangThaiGachNo"};
            Object[] valueofParameter = new Object[6]{CongNo.KhachHangId, CongNo.HoTen, CongNo.SoTien, CongNo.Thang, CongNo.Nam, CongNo.TrangThaiGachNo};
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Sửa thông tin chức năng
        /// </summary>
        /// <param name="BangGia">
        /// MaChucNang  nvarchar(10)
        /// TenChucNang  nvarchar(255)
        /// </param>
        public int Sua(clsCongNoCauLongDTO CongNo)
        {
            int i = -1;
            string sql = "sp_UpdateCongNoCauLong";
            string[] ParameterColection = new string[7] {"@CongNoId", "@KhachHangId", "@HoTen", "@SoTien", "@Thang", "@Nam", "@TrangThaiGachNo" };
            Object[] valueofParameter = new Object[7] {CongNo.CongNoId, CongNo.KhachHangId, CongNo.HoTen, CongNo.SoTien, CongNo.Thang, CongNo.Nam, CongNo.TrangThaiGachNo };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        public int CapNhatCongNoDaThuTien(decimal CongNoId, decimal PhieuThuId , double SoTienThu)
        {
            int i = -1;
            string sql = "sp_UpdateCongNoCauLongDaThuTien";
            string[] ParameterColection = new string[3] { "@CongNoId", "@PhieuThuId", "@SoTienThu" };
            Object[] valueofParameter = new Object[3] { CongNoId, PhieuThuId, SoTienThu };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        public int CapNhatCongNoKhiXoaPhieuThu( decimal PhieuThuId)
        {
            int i = -1;
            string sql = "sp_UpdateCongNoCauLongDaXoaPhieuThu";
            string[] ParameterColection = new string[1] {  "@PhieuThuId" };
            Object[] valueofParameter = new Object[1] {  PhieuThuId };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Xóa chức năng
        /// </summary>
        /// <param name="MaChucNang">Mã chức năng</param>
        public int Xoa(int CongNoId)
        {
            int i = -1;
            string sql = "sp_DeleteCongNoCauLong";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@CongNoId";
            valueofParameter[0] = CongNoId;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }
    }
}
